﻿CREATE PROCEDURE admin.s_Make_UserNameDefault#Init
AS
DECLARE @Text nvarchar(max),@NL char SET @NL = CHAR(10)
SET @Text = 
(
SELECT 
'IF @Drop = 1 ALTER TABLE '+t.name+' DROP CONSTRAINT '+dc.name+@NL+
'ELSE ALTER TABLE '+t.name+' ADD CONSTRAINT '+dc.name+' DEFAULT '+dc.definition+' FOR '+c.name+@NL+@NL
 FROM sys.default_constraints dc
INNER JOIN sys.columns c ON c.object_id = dc.parent_object_id AND dc.parent_column_id = c.column_id
INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE definition LIKE '%[[]UserName]%' FOR XML PATH('')
)

SET @Text = CASE WHEN OBJECT_ID('admin.s_Make_UserNameDefault') IS NULL THEN 'CREATE' ELSE 'ALTER' END+' PROCEDURE admin.s_Make_UserNameDefault
	@Drop bit
AS
' + @Text
PRINT @Text
EXEC(@Text)



